﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ABDLight
{
    public class ExcelFunctions
    {
        public static void WriteFile(string FileName = "test.xlsx", DataTable dt = null, string SheetName = "Sheet1")
        {
            if (dt == null) { return; }

            IWorkbook workbook = new XSSFWorkbook();
            ISheet worksheet = workbook.CreateSheet(SheetName);


            IRow row = worksheet.CreateRow(0);
            DataRow dt_row = dt.Rows[0];

            for (int colnum = 0; colnum < dt.Columns.Count; colnum++)
            {
                ICell Cell = row.CreateCell(colnum);
                Cell.SetCellValue(dt.Columns[colnum].ColumnName);
            }

            for (int rownum = 0; rownum < dt.Rows.Count; rownum++)
            {
                row = worksheet.CreateRow(rownum + 1);
                dt_row = dt.Rows[rownum];
                for (int celnum = 0; celnum < dt.Columns.Count; celnum++)
                {
                    try
                    {
                        ICell Cell = row.CreateCell(celnum);
                        if (!string.IsNullOrEmpty(dt_row[celnum].ToString()))
                        {
                            Cell.SetCellValue(dt_row[celnum].ToString());
                        }
                        else
                        {
                            Cell.SetCellValue("");
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Ошибка: " + ex.Message);
                    }
                }
            }

            FileStream sw = File.Create(FileName);
            workbook.Write(sw);
            sw.Close();
        }

  
        public static DataTable ReadFile(string FilePath = "", string SheetName = "",bool HeaderInText = true,string ExcelType = "XLSX", int NRow = -1)
        {
            if (string.IsNullOrEmpty(FilePath) ) { return new DataTable(); }
            if (!File.Exists(FilePath)) { return new DataTable(); }
            DataTable dt = new DataTable();

            if (ExcelType == "XLSX")
            {
                XSSFWorkbook hssfworkbook;
                ISheet sheet;

                using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new XSSFWorkbook(file);
                    if (string.IsNullOrEmpty(SheetName))
                    {
                        sheet = hssfworkbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = hssfworkbook.GetSheet(SheetName);
                    }
                }



                if (sheet.GetRow(0) == null) return dt;
                IRow headerRow = sheet.GetRow(0);
                IEnumerator rows = sheet.GetRowEnumerator();

                int colCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;
                frmLoad.RecordCountInFile = rowCount;
                if (NRow == -1) { 
                    NRow = rowCount;
                }
                else if (NRow < rowCount)
                {
                    rowCount = NRow;
                }

                for (int c = 0; c < colCount; c++)
                    dt.Columns.Add(headerRow.GetCell(c).ToString());


                int ind = 0;
                while (rows.MoveNext() && rowCount >= ind)
                {

                    IRow row = (XSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    if (HeaderInText)
                        if (row.RowNum == 0)
                            continue;

                    for (int i = 0; i < colCount; i++)
                    {
                        ICell cell = row.GetCell(i);

                        if (cell != null)
                            dr[i] = cell.ToString();
                    }
                    dt.Rows.Add(dr);
                    ind++;
                }
            }
            if (ExcelType == "XLS")
            {
                HSSFWorkbook hssfworkbook;
                ISheet sheet;

                using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                    if (string.IsNullOrEmpty(SheetName))
                    {
                        sheet = hssfworkbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = hssfworkbook.GetSheet(SheetName);
                    }
                }


                if (sheet.GetRow(0) == null) return dt;
                IRow headerRow = sheet.GetRow(0);
                IEnumerator rows = sheet.GetRowEnumerator();

                int colCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;
                frmLoad.RecordCountInFile = rowCount;
                if (NRow == -1) { 
                    NRow = rowCount;
                }
                else if (NRow < rowCount)
                {
                    rowCount = NRow;
                }

                for (int c = 0; c < colCount; c++)
                    dt.Columns.Add(headerRow.GetCell(c).ToString());

                int ind = 0;
                while (rows.MoveNext() && rowCount >= ind)
                {

                    IRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();

                    if (HeaderInText)
                        if (row.RowNum == 0)
                            continue;

                    for (int i = 0; i < colCount; i++)
                    {
                        ICell cell = row.GetCell(i);

                        if (cell != null)
                            dr[i] = cell.ToString();
                    }
                    dt.Rows.Add(dr);
                    ind++;
                }
            }
            return dt;

        }
    }
}
